package com.sl.au.repository;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;
import com.sl.au.entity.CustomerVisiting;

@Transactional(readOnly = true)
public interface CustomerVisitingRepository extends JpaRepository<CustomerVisiting, String> {
	
	//获得客户的来访记录
	@Query(" select v from CustomerVisiting v where v.customer.customerName like '%'+?1+'%' "
			+"or v.customer.customerCode like '%'+?1+'%' " 
			+"or v.operator like '%'+?1+'%' "
			+"or v.project like '%'+?1+'%' "
			+"or v.customer.id like '%'+?1+'%' "
			+"or v.chance like '%'+?1+'%'")
	Page<CustomerVisiting> findVisit(String key, Pageable pageable);

	//得到查询的条数
	@Query(" select count(v) from CustomerVisiting v where v.customer.customerName like '%'+?1+'%' "
			+ "or v.customer.customerCode like '%'+?1+'%' " 
			+ "or v.operator like '%'+?1+'%' "
			+"or v.project like '%'+?1+'%' "
			+"or v.customer.id like '%'+?1+'%' "
			+ "or v.chance like '%'+?1+'%'")
	Long getCount(String key);
	
	////获得本部门的客户来访
	@Query(" select v from CustomerVisiting v where (v.customer.customerName like '%'+?1+'%' "
			+"or v.customer.customerCode like '%'+?1+'%' " 
			+"or v.operator like '%'+?1+'%' "
			+"or v.project like '%'+?1+'%' "
			+"or v.chance like '%'+?1+'%') and v.saleArea.superior=?2")
	Page<CustomerVisiting> findVisit(String key,String superior, Pageable pageable);
	
	@Query(" select count(v) from CustomerVisiting v where (v.customer.customerName like '%'+?1+'%' "
			+"or v.customer.customerCode like '%'+?1+'%' " 
			+"or v.operator like '%'+?1+'%' "
			+"or v.project like '%'+?1+'%' "
			+"or v.chance like '%'+?1+'%') and v.saleArea.superior=?2")
	Long getCount(String key,String superior);
	
    ////获得本部门的本片区客户来访
	@Query(" select v from CustomerVisiting v where (v.customer.customerName like '%'+?1+'%' "
			+"or v.customer.customerCode like '%'+?1+'%' " 
			+"or v.operator like '%'+?1+'%' "
			+"or v.project like '%'+?1+'%' "
			+"or v.chance like '%'+?1+'%') and v.saleArea.superior=?2 and v.saleArea.areaName=?3")
	Page<CustomerVisiting> findVisit(String key,String superior, String areaName, Pageable pageable);
	
	@Query(" select count(v) from CustomerVisiting v where (v.customer.customerName like '%'+?1+'%' "
			+"or v.customer.customerCode like '%'+?1+'%' " 
			+"or v.operator like '%'+?1+'%' "
			+"or v.project like '%'+?1+'%' "
			+"or v.chance like '%'+?1+'%') and v.saleArea.superior=?2 and v.saleArea.areaName=?3")
	Long getCount(String key,String superior,String areaName);
	
////获得本部门的本人客户来访
	@Query(" select v from CustomerVisiting v where (v.customer.customerName like '%'+?1+'%' "
			+"or v.customer.customerCode like '%'+?1+'%' " 
			+"or v.operator like '%'+?1+'%' "
			+"or v.project like '%'+?1+'%' "
			+"or v.chance like '%'+?1+'%') and v.user.id = ?2")
	Page<CustomerVisiting> findVisit1(String key, String userId, Pageable pageable);
	
	@Query(" select count(v) from CustomerVisiting v where (v.customer.customerName like '%'+?1+'%' "
			+"or v.customer.customerCode like '%'+?1+'%' " 
			+"or v.operator like '%'+?1+'%' "
			+"or v.project like '%'+?1+'%' "
			+"or v.chance like '%'+?1+'%') and v.user.id = ?2")
	Long getCount1(String key, String userId);
	
	/////获得与客户相关的来访
	@Query(" select v from CustomerVisiting v where v.customer.id=?1 and"
			+ "(v.customer.customerName like '%'+?2+'%' "
			+ "or v.customer.customerCode like '%'+?2+'%' " 
			+ "or v.operator like '%'+?2+'%'"
			+ "or v.project like '%'+?2+'%'"
			+ "or v.chance like '%'+?2+'%')")
	Page<CustomerVisiting> findCustomerVisit(String customerId,String key, Pageable pageable);
	
	@Query(" select count(v) from CustomerVisiting v where v.customer.id=?1 and"
			+ "(v.customer.customerName like '%'+?2+'%' "
			+ "or v.customer.customerCode like '%'+?2+'%' " 
			+ "or v.operator like '%'+?2+'%'"
			+ "or v.project like '%'+?2+'%'"
			+ "or v.chance like '%'+?2+'%')")
	Long getCustomerCount(String customerId,String key);

	CustomerVisiting findById(String id);
	
	//获得客户来访的listby客户ID
	@Query(" select v from CustomerVisiting v where v.customer.id= ?1")
	List<CustomerVisiting> getVisitsById(String CustomerId);
}
